package com.shudai.largescreen.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shudai.largescreen.pojo.User;
import com.shudai.largescreen.pojo.dto.AreaAchievementDto;
import com.shudai.largescreen.pojo.vo.AreaAchievementVo;
import com.shudai.largescreen.pojo.vo.ChartDataCityVo;
import com.shudai.largescreen.pojo.vo.NumberAmount;
import com.shudai.largescreen.pojo.vo.ReCustomerVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface RegionMapper  extends BaseMapper<User> {

    @Select({
            "SELECT sum(o.amount) amount ",
            "FROM sd_order o where o.order_state in(2,3,13) and o.on_cancel  = 0  "
    })
    String findAllRegionCity();


    @Select({

            "<script>",
            " SELECT ",
            "   DATE_FORMAT( sd.created_at, '%d' ) AS name,  ",
            "             count(sd.id) as data  ",
            "FROM ",
            " sd_visit_user_record sd ,sd_tool_man_info su  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and su.city = #{city} ",
            "</if> ",

            "<if test='district != null and district != \"\" '> ",
            "and su.district = #{district} ",
            "</if> ",
            "and ",
            " sd.tool_man_id = su.user_id  ",
            " ",
            "and sd.created_at >= #{startDate} ",
            "</where> ",
            "GROUP BY name ",
            "ORDER BY name ",

            "</script>",
    })
    List<NumberAmount> findVistWeakRate(@Param("city") String city, @Param("district") String district, @Param("startDate") LocalDateTime startDate);

    @Select({

            "<script>",
            " SELECT ",
            "   DATE_FORMAT( sd.order_time, '%d' ) AS name,  ",
            "             count(sd.id) as data  ",
            "FROM ",
            " sd_order sd ,sd_tool_man_info su  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and su.city = #{city} ",
            "</if> ",

            "<if test='district != null and district != \"\" '> ",
            "and su.district = #{district} ",
            "</if> ",
            "and ",
            " sd.tool_man_id = su.user_id  ",
            " ",
            "and sd.created_at >= #{startDate} ",
            "</where> ",
            "GROUP BY name ",
            "ORDER BY name ",

            "</script>",
    })
    List<NumberAmount> findOrderWeakRate(@Param("city") String city, @Param("district") String district, @Param("startDate") LocalDateTime startDate);


    @Select({
            "<script>",
            " SELECT ",
            "   st.province areaName ,",
            " st.city district ,count(o.id) orderNumber ,sum(o.amount) transactionAmount,",
            " count(DISTINCT su.dept_id) distributorNumber",
            "FROM ",
            " sd_tool_man_info st ",
            " LEFT JOIN sys_user su ON su.id = st.user_id ",
            " LEFT JOIN sys_dept sd ON sd.id = su.dept_id  ",
            "LEFT JOIN sd_order o  on o.tool_man_id = su.id ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and st.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and st.province = #{province} ",
            "</if> ",

            "<if test='status != null and status != \"\" '> ",
            "and sd.enabled = #{status} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and sd.name = #{teamName} ",
            "</if> ",

            "and sd.created_at BETWEEN #{startDate} and #{endDate} and o.order_state  in (2,3,12) and o.on_cancel  = 0  ",
            "</where> ",
            "GROUP BY st.province,st.city  ",

            "</script>",
    })
    List<AreaAchievementVo> findDistributor(@Param("province") String city,
                                            @Param("city") String district,
                                            @Param("status") String status,
                                            @Param("startDate") LocalDateTime startDate,
                                            @Param("endDate") LocalDateTime endDate,
                                            @Param("teamName") String teamName);


    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(o.id),0)  orderNumber ,sum(o.amount) transactionAmount ",
            " FROM ",
            " sd_user_record sr  ",
            " LEFT join sd_order o on o.user_record_id = sr.id ",
            " LEFT join sys_user u on u.id = sr.id ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and sr.shop_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sr.shop_province = #{province} ",
            "</if> ",



            "and o.order_time BETWEEN #{startDate} and #{endDate} and o.order_state  in (2,3,12) and o.on_cancel = 0 ",
            "</where> ",

            "</script>",
    })
    AreaAchievementVo findDistributors(@Param("province") String city,
                                            @Param("city") String district,
                                            @Param("status") String status,
                                            @Param("startDate") LocalDateTime startDate,
                                            @Param("endDate") LocalDateTime endDate,
                                            @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT ",
            "  count(o.id) orderNumber ,sum(o.amount) transactionAmount ",
            " FROM ",
            "  sys_user u ",
            " LEFT join sd_order o on o.tool_man_id = u.id ",
            " LEFT join sd_tool_man_info sd on sd.user_id = u.id ",
            " LEFT join sys_dept d sd on d.id = u.id ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and d.name = #{teamName} ",
            "</if> ",


            "and o.order_time BETWEEN #{startDate} and #{endDate} and o.order_state  in (2,3,12) and o.on_cancel = 0 ",
            "</where> ",

            "</script>",
    })
    AreaAchievementVo findDistributorss(@Param("province") String city,
                                       @Param("city") String district,
                                       @Param("status") String status,
                                       @Param("startDate") LocalDateTime startDate,
                                       @Param("endDate") LocalDateTime endDate,
                                       @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT ",
            "   st.province area ,",
            " st.city district ,",
            " count(sr.id) total",
            "FROM ",
            " sd_tool_man_info st ",
            " LEFT JOIN sys_user su ON su.id = st.user_id ",
            " LEFT JOIN sd_user_record sr ON sr.tool_man_id = st.user_id ",
            " LEFT JOIN sys_dept sd ON sd.id = su.dept_id  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and st.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and st.province = #{province} ",
            "</if> ",

            "<if test='status != null and status != \"\" '> ",
            "and sd.enabled = #{status} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and sd.name = #{teamName} ",
            "</if> ",
            "and ",
            " PERIOD_DIFF( date_format( now( ), '%Y%m' ), date_format( sd.created_at, '%Y%m' ) ) =1",
            "and sd.created_at BETWEEN #{startDate} and #{endDate}",
            "</where> ",
            "GROUP BY st.city,st.province ",

            "</script>",
    })
    List<AreaAchievementDto> findNowMoth(@Param("province") String city,
                                         @Param("city") String district,
                                         @Param("status") String status,
                                         @Param("startDate") LocalDateTime startDate,
                                         @Param("endDate") LocalDateTime endDate,
                                         @Param("teamName") String teamName);


    @Select({
            "<script>",
            " SELECT ",
            "   st.province area ,",
            " st.city district ,",
            " count(sr.id) total",
            "FROM ",
            " sd_tool_man_info st ",
            " LEFT JOIN sys_user su ON su.id = st.user_id ",
            " LEFT JOIN sd_user_record sr ON sr.tool_man_id = st.user_id ",
            " LEFT JOIN sys_dept sd ON sd.id = su.dept_id  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and st.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and st.province = #{province} ",
            "</if> ",

            "<if test='status != null and status != \"\" '> ",
            "and sd.enabled = #{status} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and sd.name = #{teamName} ",
            "</if> ",
            "and ",
            " PERIOD_DIFF( date_format( now( ), '%Y%m' ), date_format( sd.created_at, '%Y%m' ) ) =1",
            "and sd.created_at BETWEEN #{startDate} and #{endDate}",
            "</where> ",
            "GROUP BY st.city,st.province ",

            "</script>",
    })
    List<AreaAchievementDto> findNowMoths(@Param("province") String city,
                                         @Param("city") String district,
                                         @Param("status") String status,
                                         @Param("startDate") LocalDateTime startDate,
                                         @Param("endDate") LocalDateTime endDate,
                                         @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(sr.id),0) total",
            "FROM ",
            " sd_user_record sr " +
                    " ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and sr.shop_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sr.shop_province = #{province} ",
            "</if> ",

            "and sr.created_at BETWEEN #{startDate} and #{endDate}",
            "</where> ",

            "</script>",
    })
    Integer findNowMothss(@Param("province") String city,
                                          @Param("city") String district,
                                          @Param("status") String status,
                                          @Param("startDate") LocalDateTime startDate,
                                          @Param("endDate") LocalDateTime endDate,
                                          @Param("teamName") String teamName);


    @Select({
            "<script>",
            "SELECT  IFNULL(count(distinct u.dept_id),0)  FROM sys_user u,sd_tool_man_info sd  , sys_dept de " +
                    "WHERE  u.id = sd.user_id and sd.province = #{province} and sd.city = #{city} " +
                    "  and de.id = u.dept_id and de.created_at &lt; #{endDate} ",

            "</script>",
    })
    Integer findDistributorNumber(@Param("province") String city,
                                     @Param("city") String district,
                                  @Param("endDate") LocalDateTime endDate);

    @Select({
            "<script>",
            " SELECT ",
            "   st.province area ,",
            " st.city district ,",
            " count(o.id) total",
            "FROM ",
            " sd_tool_man_info st ",
            " LEFT JOIN sys_user su ON su.id = st.user_id ",
            " LEFT JOIN sd_order o ON o.tool_man_id = st.user_id ",
            " LEFT JOIN sys_dept sd ON sd.id = su.dept_id  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and st.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and st.province = #{province} ",
            "</if> ",

            "<if test='status != null and status != \"\" '> ",
            "and sd.enabled = #{status} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and sd.name = #{teamName} ",
            "</if> ",
            "and ",
            " PERIOD_DIFF( date_format( now( ), '%Y%m' ), date_format( o.order_time, '%Y%m' ) ) =1",
            "and sd.created_at BETWEEN #{startDate} and #{endDate}",
            "</where> ",
            "GROUP BY st.city,st.province ",

            "</script>",
    })
    List<AreaAchievementDto> findBeforeOrder(@Param("province") String city,
                                             @Param("city") String district,
                                             @Param("status") String status,
                                             @Param("startDate") LocalDateTime startDate,
                                             @Param("endDate") LocalDateTime endDate,
                                             @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(o.id),0) total",
            "FROM ",

            " sd_order o  ",

            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and o.receiver_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "and o.order_time BETWEEN #{startDate} and #{endDate}  and o.order_state in (2,3,12) and o.on_cancel = 0",
            "</where> ",

            "</script>",
    })
    int findBeforeOrders(@Param("province") String city,
                                             @Param("city") String district,
                                             @Param("status") String status,
                                             @Param("startDate") LocalDateTime startDate,
                                             @Param("endDate") LocalDateTime endDate,
                                             @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT ",
            "   st.province area ,",
            " st.city district ,",
            " count(o.id) total",
            "FROM ",
            " sd_tool_man_info st ",
            " LEFT JOIN sys_user su ON su.id = st.user_id ",
            " LEFT JOIN sd_order o ON o.tool_man_id = st.user_id ",
            " LEFT JOIN sys_dept sd ON sd.id = su.dept_id  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and st.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and st.province = #{province} ",
            "</if> ",

            "<if test='status != null and status != \"\" '> ",
            "and sd.enabled = #{status} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and sd.name = #{teamName} ",
            "</if> ",
            "and ",
            " DATE_FORMAT( o.order_time, '%Y%m' ) = DATE_FORMAT(",
            "CURDATE( ),",
            "'%Y%m' ",
            ")",
            "and sd.created_at BETWEEN #{startDate} and #{endDate}",
            "</where> ",
            "GROUP BY st.city,st.province ",

            "</script>",
    })
    List<AreaAchievementDto> findNowOrder(@Param("province") String city,
                                          @Param("city") String district,
                                          @Param("status") String status,
                                          @Param("startDate") LocalDateTime startDate,
                                          @Param("endDate") LocalDateTime endDate,
                                          @Param("teamName") String teamName);

    @Select({
            "<script>",
            " SELECT ",
            " IFNULL(count(o.id),0) total",
            "FROM ",

            "  sd_order o  ",

            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and o.receiver_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and  o.receiver_province = #{province} ",
            "</if> ",

            "and o.order_time BETWEEN #{startDate} and #{endDate}  and o.order_state in (2,3,12) and o.on_cancel = 0 ",
            "</where> ",


            "</script>",
    })
    int findNowOrders(@Param("province") String city,
                                          @Param("city") String district,
                                          @Param("status") String status,
                                          @Param("startDate") LocalDateTime startDate,
                                          @Param("endDate") LocalDateTime endDate,
                                          @Param("teamName") String teamName);


    @Select({
            "<script>",
            "SELECT  count(o.user_id )>1 total,",
            "sd.province  province,sd.city city,o.user_record_id idNumber,",
            "max(o.order_time) lastOrderTime,o.receiver_shop_name name,",
            "o.tool_man_name salesman,o.total_number goodsNumber ",
            " ,o.buyer_avatar  img",
            "from ",
            "sd_order o,sd_tool_man_info sd ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",
            " and ",
            "o.tool_man_id = sd.user_id and o.order_state = 3 ",
            " and o.user_record_id is not null " ,
                    "and o.tool_man_id is not null ",
            "</where> ",
            "GROUP BY  sd.province,sd.city,o.user_record_id,o.receiver_shop_name,o.tool_man_name,o.total_number,o.buyer_avatar ",
            "ORDER BY max(o.order_time) desc ",
            "</script> "

    })
    List<ReCustomerVo> getReCustomerIOrder(@Param("province") String city,
                                           @Param("city") String district);

    @Select({
            "<script>",
            "SELECT  t.*" ,
            "from ( SELECT t.buyer_avatar img,t.receiver_shop_name name,",
            "t.receiver_province province,t.receiver_city city,",
            "t.tool_man_name salesman, ",
            "t.order_time lastOrderTime,t.user_record_id idNumber ,",
            "row_number() over (partition by t.user_record_id order by t.order_time ) as seqnum ",
            "from  ",
            "sd_order t  ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and t.receiver_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and t.receiver_province= #{province} ",
            "</if> ",

            " and ",
            " t.order_state in(2,3,12) and t.on_cancel  = 0  ",
            "GROUP BY  t.buyer_avatar,t.receiver_shop_name,t.receiver_province,t.receiver_city,t.receiver_district,t.tool_man_name , t.order_time,t.user_record_id ",
            "ORDER BY t.user_record_id",
            "</where> )t  where  seqnum <![CDATA[ <= ]]> 2",

            "</script> "

    })
    List<ReCustomerVo> findReCustomerTime(@Param("province") String city,
                                          @Param("city") String district);



    @Select({
            "<script>",
            " SELECT ",
            "  sum(o.amount) date,o.receiver_province area,o.receiver_city district ",
            "     ",
            "FROM ",
            " sd_order o  ",
            "<where> 1=1 ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "<if test='city != null and city != \"\" '> ",
            "and o.receiver_city = #{city} ",
            "</if> ",

            "and o.order_time > #{startDate} and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "</where> ",
            "GROUP BY o.receiver_province,o.receiver_city ",
            "ORDER BY date desc",

            "</script>",
    })
    ChartDataCityVo findRegionRank(@Param("province") String province,
                                         @Param("city") String city,
                                         @Param("startDate") LocalDateTime startDate);


    @Select({
            "<script>",
            " SELECT ",
            "  sum(o.amount) date,o.receiver_province area ",
            "     ",
            "FROM ",
            " sd_order o  ",
            "<where> 1=1 ",
            " and o.receiver_province = #{province} ",
            " and o.order_time > #{startDate} and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "</where> ",
            "GROUP BY o.receiver_province",

            "</script>",
    })
    ChartDataCityVo findRegionRank1(@Param("province") String province,
                                   @Param("startDate") LocalDateTime startDate);



    @Select({
            "<script>",
            " SELECT ",
            " count(v.id) date,u.shop_province area ,u.shop_city district" ,
            "FROM ",
            " sd_visit_user_record v , sd_user_record u ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and u.shop_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and u.shop_province = #{province} ",
            "</if> ",

            " and ",

            " v.user_record_id = u.id  ",
            "and v.created_at between #{startDate} and #{endDate}",

            "</where>",
            "GROUP BY ",
            "  u.shop_province ,u.shop_city ",

            "</script>"
//            "  AND YEAR(order_time)=YEAR(NOW()) ",
    })
    ChartDataCityVo findConversion(@Param("province") String province,
                                         @Param("city") String city,
                                         @Param("startDate") LocalDateTime startDate,
                                         @Param("endDate") LocalDateTime endDate);



    @Select({
            "<script>",
            "SELECT ",
            "  IFNULL(count(o.id),0) date,o.receiver_province area ,o.receiver_city district ",
            "from sd_order o  ",
            " <where> 1=1  ",

            "<if test='city != null and city != \"\" '> ",
            "and o.receiver_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "and o.order_state in (2,3,12) and o.on_cancel  = 0  ",
            " and o.order_time  between #{startDate} and #{endDate} ",
            " </where> ",
            "GROUP BY  o.receiver_province,o.receiver_city",
            "</script>"
    })
    ChartDataCityVo findOrders(@Param("province") String province,
                                     @Param("city") String city,
                                     @Param("startDate") LocalDateTime startDate,
                                     @Param("endDate") LocalDateTime endDate);


    @Select({
            "<script>",
            " SELECT ",
            " count(u.id) date,u.shop_province area ,u.shop_city district" ,
            "FROM ",
            " sd_user_record u ",
            "<where> 1=1 ",

            "<if test='city != null and city != \"\" '> ",
            "and u.shop_city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and u.shop_province = #{province} ",
            "</if> ",

            "and u.created_at between #{startDate} and #{endDate}",

            "</where>",
            "GROUP BY ",
            "  u.shop_province ,u.shop_city ",

            "</script>"
//            "  AND YEAR(order_time)=YEAR(NOW()) ",
    })
    ChartDataCityVo findUserRe(@Param("province") String province,
                                     @Param("city") String city,
                                     @Param("startDate") LocalDateTime startDate,
                                     @Param("endDate") LocalDateTime endDate);

    @Select({
            " SELECT ",
            "u.shop_city district" ,
            "FROM ",
            " sd_visit_user_record v , sd_user_record u ",
            " where ",
            " v.user_record_id = u.id  ",
            "and u.shop_province = #{province} ",
            "GROUP BY ",
            " u.shop_city ",

    })
    List<String> findCity(@Param("province") String province);

    @Select({
            " SELECT ",
            "o.receiver_city district" ,
            "FROM ",
            " sd_order o",
            " where ",
            " o.receiver_province = #{province} ",
            "GROUP BY ",
            " o.receiver_city  ",

    })
    List<String> findAllCity(@Param("province") String province);

}
